﻿using System;
using System.ServiceProcess;
using Schedule;
using devicesDll;
using Database;
using Microsoft.Win32;
using System.Collections.Generic;
using System.Net;
using System.Linq;
using System.Text;

namespace ControlService
{
    
    /// <summary>
    /// <para>All Posible Commands, which can be sent to the service</para>
    /// <para>unkown: if no valid command was sent</para>
    /// <para>ReloadSettings: read Settings <example>Example: Interval</example></para>
    /// <para>Search: start a new Search</para>
    /// <para>ChangeConfigFile: Read the new Path for the ConfigFile</para>
    /// <para>Pause: Pauses the Service</para>
    /// <para>Continue: Continues the Service</para>
    /// <para>Stop: Permanently Stops the Service</para>
    /// </summary>
    enum Command { unknown = 127, ReloadSettings, Search, ChangeConfigFile}

    /// <summary>
    /// Flags for occured Errors (for database)
    /// </summary>
    [Flags]
    enum GlobalFlags { None = 0, UnknownError = 0x01, FailedToRemoveSwitchError = 0x02, FailedToAddSwitchError = 0x04, FinishedUpdateSettings = 0x08, FinishedUpdateCustomQueries = 0x10,ServiceStarting = 0x20, FinishedStarting = 0x40,ServicePaused = 0x80,ServiceContinued=0x100,ServiceStopped=0x200, FailedToApplySettings=0x400 }
    enum DeviceFlags
    {
        None = 0, 
        NotFoundInInventory = 0x01, SwitchportChanged = 0x02, ConnectionFailed = 0x04, RoomPatchpanelNotFound = 0x08,
        DeviceTypeChanged = 0x10, RemovedFromQueryList=0x20,
        RamChanged = 0x100, OSChanged = 0x200,
        PaperInputDescriptionChanged = 0x10000, SerialNumberChanged = 0x20000, PrinterStateChanged = 0x40000, TonerChanged = 0x80000, 
        TonerSupplyMaximumChanged = 0x100000
    }
    enum SwitchFlags { None = 0, SwitchportChanged = 0x01, ConnectionFailed=0x02 }
    enum SwitchPortFlags { None = 0, SwitchportCreated = 0x01, VlanChanged = 0x02 }

    public partial class ControlService : ServiceBase
    {
        #region Constants
        const int supplierNotFound = -1;
        const int eventLogMessageLength = 16000;
        #endregion

        #region Variables
        ScheduleTimer timer = new ScheduleTimer();
        Configuration currentSettings = null;
        Devices devices;
        DBConnect databaseConnection;
        //List<IPAddress> switchList = new List<IPAddress>();
        //List<int> vlanList = new List<int>();
        List<IpVlan> switchList = new List<IpVlan>();
        int flagID = 1;
        int gpfID = 0;
        #endregion

        #region Constructor
        public ControlService()
        {
            InitializeComponent();
        }
        #endregion

        #region Methods
        private string convertListToString(params object[] list)
        {
            StringBuilder sb = new StringBuilder();
            foreach (Array itemList in list)
            {
                if (itemList != null)
                {
                    sb.AppendLine("List: " + itemList.ToString() + "\n Count: " + itemList.Length);
                    foreach (object item in itemList)
                    {
                        sb.AppendLine(" Item: " + item.ToString());
                    }
                    sb.AppendLine();
                }
                else
                    sb.AppendLine("List: null\n");
            }
            return sb.ToString();
        }

        /// <summary>
        /// Initializes the Service
        /// </summary>
        private void Initialize()
        {
            string SCPPath = "";
            #region connect to database
            try
            {
                #region get information from registry
                RegistryKey key = Registry.LocalMachine.OpenSubKey(@"Software\HTL Rankweil\InvIT");
                string server = (string)key.GetValue("DataBaseServer");
                string dataBase = (string)key.GetValue("DataBase");
                string username = (string)key.GetValue("DataBaseUsername");
                string password = (string)key.GetValue("DataBasePassword");
                SCPPath = (string)key.GetValue("SCPPath", "");
                #endregion
                databaseConnection = new DBConnect(server, dataBase, username, password); //initialize database connection
                databaseConnection.OpenConnection(); //try open connection
            }
            catch (Exception ex)
            {
                eventLog.WriteEntry("Database failure: " + ex.Message, System.Diagnostics.EventLogEntryType.Error); //write error to EventLog
                this.Stop(); //stop service (fatal error)
            }
            #endregion


            gpfID = databaseConnection.InsertIntoFlag((int)GlobalFlags.ServiceStarting, "ServiceStateChanged", DateTime.Now); //add flag to table

            try
            {
                if (SCPPath != "" && !databaseConnection.ExistsValue("SCPPath"))
                {
                    databaseConnection.AddValue("SCPPath",SCPPath);
                }

                GetSettingsFromDatabase(); //get and apply settings
            }
            catch (Exception ex)
            {
                eventLog.WriteEntry(ex.Message + ex.StackTrace, System.Diagnostics.EventLogEntryType.Error); //write error to eventLog
                #region Update Error Flag
                int tmpFlag = databaseConnection.GetFlag(gpfID); //get progress flag
                tmpFlag |= (int)GlobalFlags.FailedToApplySettings; //add database update error flag
                databaseConnection.UpdateFlag(gpfID, tmpFlag); //update database
                #endregion
            }

            try
            {

                #region initialize devices
                ResolveSwitchVlans(); //get switches/vlans
                devices = new Devices(switchList.ToArray(), currentSettings.Timeout, ConvertStringToIP(currentSettings.IPNetDHCP), IPAddress.Parse(currentSettings.IPDHCP), currentSettings.MaxThreads); //create devices object
                devices.WriteProgressMethod = new WriteProgress(WriteFullscanProgressToDatabase); //set method for database update
                if (currentSettings.Debug)
                    devices.WriteDebugMethod = new WriteDebugMessage(WriteDebugMessageToEventLog);

                GetCustomQueriesFromDatabase(); //get and apply custom queries

                #endregion


                #region initialize Timer
                timer.Elapsed += new ScheduledEventHandler(timer_Elapsed); //set method for timer event
                IScheduledItem item = new ScheduledTime(EventTimeBase.Daily, new TimeSpan(currentSettings.ScanTime.Hour, currentSettings.ScanTime.Minute, currentSettings.ScanTime.Second)); //initialize timer event
                timer.AddEvent(item); //add event
                timer.Start(); //start timer
                #endregion

                #region Update Flag
                int tmpFlag = databaseConnection.GetFlag(gpfID); //get progress flag
                tmpFlag |= (int)GlobalFlags.FinishedStarting; //add database update flag
                databaseConnection.UpdateFlag(gpfID, tmpFlag); //update database
                #endregion

            }
            catch (Exception ex)
            {
                eventLog.WriteEntry(ex.Message + ex.StackTrace, System.Diagnostics.EventLogEntryType.Error); //write error to eventLog
                #region Update Error Flag
                int tmpFlag = databaseConnection.GetFlag(gpfID); //get progress flag
                tmpFlag |= (int)GlobalFlags.UnknownError; //add database update error flag
                databaseConnection.UpdateFlag(gpfID, tmpFlag); //update database
                #endregion
            }

            while (!databaseConnection.CloseConnection()) ; //close database connection
        }

        /// <summary>
        /// Converts an array of Ip Adresses as strings to array of IPAddress objects
        /// </summary>
        /// <param name="ips">Ip Adresses as string</param>
        /// <returns>Ip Address Array of given strings</returns>
        private IPAddress[] ConvertStringToIP(string[] ips)
        {
            List<IPAddress> convertedIps = new List<IPAddress>(ips.Length); //create an array with specific length
            for (int i = 0; i < ips.Length; i++) //for every given IP Address
            {
                try
                {
                    convertedIps.Add(IPAddress.Parse(ips[i]));   //Parse
                }
                catch { }
            }
            return convertedIps.ToArray();
        }

        /// <summary>
        /// Is executed, when the timer elapses
        /// </summary>
        /// <param name="sender">sender Object</param>
        /// <param name="e">timer EventArgs</param>
        private void timer_Elapsed(object sender, ScheduledEventArgs e)
        {
            ExecuteFullScan(); //starts Full Scan
        }

        /// <summary>
        /// <para>Configures database connection for flags, starts the Full scan and writes results to the database</para>
        /// <para>Writes Errors to the eventLog</para>
        /// </summary>
        private void ExecuteFullScan()
        {

            WriteDebugMessageToEventLog("Start Full Scan");
            if (!databaseConnection.OpenConnection()) //if connection is not open
            {
                eventLog.WriteEntry("Could not Connect to Database", System.Diagnostics.EventLogEntryType.Error); //write Fatal error
                return;
            }

            #region Get Flag ID
            flagID = databaseConnection.InsertIntoFlag((int)QueryProgress.None, "FullScanProgress", DateTime.Now); //add flag to table
            gpfID = databaseConnection.InsertIntoFlag((int)GlobalFlags.None, "GlobalPurposeFlag", DateTime.Now); //add flag to table
            #endregion

            try
            {
                WriteDebugMessageToEventLog(devices.ToString());
                devices.FullScan(); //Start scaning
                WriteDebugMessageToEventLog("data mining finished");
                WriteResultsToDatabase(); //update Database
            }
            catch (Exception ex) //Error Handling
            {
                eventLog.WriteEntry(ex.Message +"\r\n"+ ex.StackTrace, System.Diagnostics.EventLogEntryType.Error); //write error to eventlog
                #region Update Error Flag
                int tmpFlag = databaseConnection.GetFlag(gpfID); //get progress flag
                tmpFlag |= (int)GlobalFlags.UnknownError; //add database update successfull flag
                databaseConnection.UpdateFlag(gpfID, tmpFlag); //update database
                #endregion
            }

            while (!databaseConnection.CloseConnection()) ; //close connection
            WriteDebugMessageToEventLog("Full Scan Finished");
        }

        /// <summary>
        /// writes a message to the event log, if debugging is enabled
        /// </summary>
        /// <param name="message">Message to writee</param>
        private void WriteDebugMessageToEventLog(string message)
        {
            try
            {
                if (currentSettings.Debug)
                {
                    int i=1;
                    while (message.Length > eventLogMessageLength)
                    {
                        string outStr = message.Substring(0, eventLogMessageLength);
                        message = message.Remove(0, eventLogMessageLength);
                        outStr += "\n\n\n Part: " + i;
                        i++;
                        eventLog.WriteEntry(outStr);
                    }
                    if (i > 1)
                        message += "\n\n\n  Part: " + i;
                    eventLog.WriteEntry(message);
                }
            }
            catch { }
        }

        #region Database Methods
        /// <summary>
        /// Searches in the database for all switches an vlans and connect it to one list of type IpVlan
        /// </summary>
        private void ResolveSwitchVlans()
        {

            #region Temporary Variables from database
            List<SwitchDB> switches = databaseConnection.GetAllSwitch(true);
            List<Vlan> vlans = databaseConnection.GetAllVlan();
            List<SwitchHasVlan> switchVlan = databaseConnection.GetAllSwitchHasVlan();
            #endregion

            foreach (SwitchHasVlan sv in switchVlan)
            {
                Vlan tempVlan = (from vl in vlans where vl.Id == sv.VlanId select vl).SingleOrDefault(); //get corresponding vlan
                SwitchDB tempSwitch = (from sw in switches where sw.Id== sv.SwitchId select sw).SingleOrDefault(); //get corresponding switch

                IPAddress ipAddr = null;
                try
                {
                    ipAddr = IPAddress.Parse(tempSwitch.Ip);
                }
                catch { }

                if (ipAddr != null && tempVlan != default(Vlan)) //if one switch and one vlan has been found
                    switchList.Add(new IpVlan(ipAddr, tempVlan.VlanNumber)); //add switch to list
                else
                {
                    string additionalInformation = "";
                    try
                    {
                        if (tempSwitch != default(SwitchDB))
                            additionalInformation = "\nName: " + tempSwitch.Name
                                                  + "\nIP: " + tempSwitch.Ip
                                                  + "\nID: " + tempSwitch.Id;
                    }
                    catch { }
                    eventLog.WriteEntry("Failed to resolve SwitchHasVlan" + additionalInformation, System.Diagnostics.EventLogEntryType.Error);
                    #region Update Error Flag
                    int tmpFlag = databaseConnection.GetFlag(gpfID); //get progress flag
                    tmpFlag |= (int)GlobalFlags.FailedToAddSwitchError; //add database update error flag
                    databaseConnection.UpdateFlag(gpfID, tmpFlag); //update database
                    #endregion
                }
            }
        }

        /// <summary>
        /// rereads switches and vlans, connect it and update devices list
        /// </summary>
        private void UpdateSwitches()
        {
            List<IpVlan> tempSwitchList = new List<IpVlan>(switchList); //copy ipVlan list
            switchList.Clear();

            ResolveSwitchVlans(); //get switches from database
            if (tempSwitchList == switchList) //if lists are equeal, return
                return;

            //get recently added switches
            List<IpVlan> newSwitches = (from s in switchList where !tempSwitchList.Contains(s) select s).ToList();
            //get recently removed switches
            List<IpVlan> oldSwitches = (from s in tempSwitchList where !switchList.Contains(s) select s).ToList();

            #region Add new Switches
            foreach (IpVlan sw in newSwitches) 
            {
                devices.Add(new Switch(sw.Ip, sw.Vlan)); //add
            }
            #endregion

            #region Remove old Switches
            foreach (IpVlan sw in oldSwitches) //remove all old switches
            {
                Switch s = ((IEnumerable<Switch>)(devices.Where<Device>(dev => (dev.IP == sw.Ip && dev.Vlan == sw.Vlan && dev.GetType() == typeof(Switch))))).SingleOrDefault(); //get switch in devices list, which fits to the given information (IP, VLan)
                if (s != default(Switch)) //if query was successfull, exact one switch was found
                {
                    devices.Remove(s); //remove switch
                }
                else
                {
                    eventLog.WriteEntry("Failed to remove Switch", System.Diagnostics.EventLogEntryType.Error); //Write error message
                    #region Update Error Flag
                    int tmpFlag = databaseConnection.GetFlag(gpfID); //get progress flag
                    tmpFlag |= (int)GlobalFlags.FailedToRemoveSwitchError; //add database update error flag
                    databaseConnection.UpdateFlag(gpfID, tmpFlag); //update database
                    #endregion
                }
            }
            #endregion
        }

        /// <summary>
        /// Get Settings from Database, recognize changes and apply eventual changes
        /// </summary>
        private void GetSettingsFromDatabase()
        {
            Configuration settings = databaseConnection.GetAllConfiguration(); //get configuration
            if (currentSettings == null) //if there are currently no settings (at start of the service)
            {
                currentSettings = settings; //apply all settings
            }
            else
            {
                #region Recognize/Apply changes
                if (currentSettings.MaxThreads != settings.MaxThreads) //if MaxThreads changed
                {
                    devices.MaxThreads = settings.MaxThreads; //apply 
                }
                if (currentSettings.IPDHCP != settings.IPDHCP) //if DHCPServerAddress changed
                {
                    devices.DhcpServerAddress = IPAddress.Parse(settings.IPDHCP); //apply
                }
                if (currentSettings.IPNetDHCP != settings.IPNetDHCP) //if DHCPNetAddress changed
                {
                    devices.DhcpNetAddresses = ConvertStringToIP(settings.IPNetDHCP); //apply
                }
                if (currentSettings.Timeout != settings.Timeout) //if timeout changed
                {
                    devices.Timeout = settings.Timeout; //apply
                }
                if (currentSettings.Debug != settings.Debug) //if debug property changed
                {
                    if (settings.Debug)
                        devices.WriteDebugMethod = new WriteDebugMessage(WriteDebugMessageToEventLog);
                    else
                        devices.WriteDebugMethod = null;
                }
                if (currentSettings.ScanTime != settings.ScanTime) //if scanTime changed
                {
                    timer.ClearJobs(); //delete old time
                    IScheduledItem item = new ScheduledTime(EventTimeBase.Daily, new TimeSpan(currentSettings.ScanTime.Hour, currentSettings.ScanTime.Minute, currentSettings.ScanTime.Second)); //create new event
                    timer.AddEvent(item); //add to timer
                }
                #endregion

                currentSettings = settings; //save applied settings
            }
        }

        /// <summary>
        /// Get all custom queries form Database
        /// </summary>
        private void GetCustomQueriesFromDatabase()
        {
            List<CustomQueriesDB> cqdbList = databaseConnection.GetAllCustomQueries(); //get all cusom queries
            List<CustomQuery> customQueries = new List<CustomQuery>(); //create custom queries list
            foreach (CustomQueriesDB cqdb in cqdbList) //foreach query from database 
            {
                customQueries.Add(new CustomQuery(cqdb.Query, cqdb.Type)); //convert and add to custom query list for devices
            }
            devices.CustomQueriesCollection = customQueries.ToArray(); //apply to devices object

        }

        /// <summary>
        /// Gets the ID of the Addition Type
        /// </summary>
        /// <param name="typeString">Type</param>
        /// <param name="typeList">current TypeList</param>
        /// <returns>Addition Type ID</returns>
        private int GetAdditionTypeID(string typeString, List<AdditionType> typeList)
        {
            AdditionType currentType = (from at in typeList where at.Type == typeString select at).FirstOrDefault(); //get first type in list, which maches with requested type
            if (currentType == default(AdditionType)) //if no type found
            {
                AdditionType newType = new AdditionType(); //create new Type
                newType.Type = typeString; //apply typename
                newType.Id =databaseConnection.InsertIntoAdditionType(newType); //insert into database and save ID
                typeList.Add(newType); //save in local list
                return newType.Id; //return ID
            }
            else
                return currentType.Id; //return ID
         }

        /// <summary>
        /// Adds all custom results of an Device to the Database
        /// </summary>
        /// <param name="devDB">Database device Object, to connect entries with</param>
        /// <param name="dev">Query Device Object, with the custom results</param>
        /// <param name="errorID">ID of the error type</param>
        /// <param name="currentAddition">current Addition List (will not be updated)</param>
        /// <param name="currentAdditionTypes">Current List of Addition Types</param>
        private void AddCustomResults(DeviceDB devDB, Device dev, int errorID,List<Addition> currentAddition, List<AdditionType> currentAdditionTypes)
        {
            foreach (CustomResult customRes in dev.CustomResults) //foreach given result
            {
                string info;
                int typeID;
                if (customRes.ErrorOccurred) //if error
                {
                    info = customRes.ErrorMessage + "@" + customRes.ErrorQuery; //set: info message+query
                    typeID = errorID; //ID set to error ID
                }
                else //query successful
                {
                    info = customRes.Result.ToString(); //set info: result
                    typeID = GetAdditionTypeID(customRes.VariableName, currentAdditionTypes); //get ID of Addition Type
                }
                WriteAdditionalInformationWithChange(typeID, currentAddition, devDB, info, 0); //watch for change and update List, no flag and not are set
            }
        }

        /// <summary>
        /// Insert one additional information, if it has changed
        /// </summary>
        /// <param name="additionTypeId">id of the addition type of the new addition</param>
        /// <param name="currentAddition">current Addition List (will not be updated)</param>
        /// <param name="dev">Database device object, to connect the entry with</param>
        /// <param name="information">result of the addition Information</param>
        /// <param name="flag">flag which should be set, if changes occur</param>
        /// <param name="note">note which should be added, if changes occur</param>
        private void WriteAdditionalInformationWithChange(int additionTypeId, List<Addition> currentAddition, DeviceDB dev, string information, int flag)
        {
            //searches first matching Addition with device ID and addition type ordered by date descending (newest first)
            Addition add = (from ad in currentAddition where ad.AdditionTypeId == additionTypeId && ad.DeviceId == dev.Id orderby ad.Date descending select ad).FirstOrDefault();

            if (add == default(Addition)) //if no matching entry was found
            {
                add = new Addition(); //create new Addition object
                add.AdditionTypeId = additionTypeId; //apply Addition Type ID
                add.DeviceId = dev.Id; //apply Device ID
                add.Text = null; //set text to no valid value

            }

            if (add.Text != information) //if information has changed or no addition was found
            {
                dev.ChangeFlag |= flag; //set given change flag (can be 0)

                add.Text = information; //apply new information
                add.Date = DateTime.Now; //set time to now
                databaseConnection.InsertIntoAddition(add); //add to database (no update in local list)
            }
            else //if information has not changed
            {
                add.Date = DateTime.Now; //apply date to now
                databaseConnection.UpdateAddition(add.Id, add); //update in database (no update in local list)
            }
        }

        /// <summary>
        /// Write the complete devices List into the database
        /// </summary>
        private void WriteResultsToDatabase()
        {
            bool databaseUpdateSuccessful = true;
            if (!databaseConnection.OpenConnection()) //open database connection
            {
                eventLog.WriteEntry("Could not Connect to Database", System.Diagnostics.EventLogEntryType.Error);
                return;
            }
            #region Temporary Variables for complete Progress
            List<AdditionType> currentAdditionTypes = databaseConnection.GetAllAdditionType();
            List<DeviceDB> currentDevices = databaseConnection.GetAllDevice();
            List<SwitchDB> currentSwitches = databaseConnection.GetAllSwitch(false);
            List<Mac> currentMacs = databaseConnection.GetAllMac();
            List<SwitchPort> currentPorts = databaseConnection.GetAllSwitchPort();
            List<Addition> currentAddition = databaseConnection.GetAllAddition();
            List<Vlan> currentVlans = databaseConnection.GetAllVlan();
            List<Database.DeviceType> currentDeviceTypes = databaseConnection.GetAllDeviceType();
            List<PatchPanel> currentPatchPanels = databaseConnection.GetAllPatchPanel();
            List<Room> currentRooms = databaseConnection.GetAllRoom();
            List<Relocation> currentRelocations = databaseConnection.GetAllRelocation();
            int errorID = GetAdditionTypeID("Error", currentAdditionTypes);
            int defaultSupplierID = GetDefaultSupplierID();
            #endregion 

            string allLists = convertListToString(currentAddition.ToArray(), 
                currentAdditionTypes.ToArray(), 
                currentDevices.ToArray(), 
                currentDeviceTypes.ToArray(), 
                currentMacs.ToArray(), 
                currentPatchPanels.ToArray(), 
                currentPorts.ToArray(), 
                currentRelocations.ToArray(), 
                currentRooms.ToArray(), 
                currentSwitches.ToArray(), 
                currentVlans.ToArray());

            WriteDebugMessageToEventLog("Write Results to database: \n" + devices.ToString());
            WriteDebugMessageToEventLog(allLists);
            #region Write
            //Foreach switch write Devices which are connected to this switch
            foreach (Switch switchItem in devices.OfType<Switch>())
            {
                //Get database switch which represents the query switch
                SwitchDB tempSwitch = default(SwitchDB);
                try
                {
                    tempSwitch = (from si in currentSwitches where si.Ip == switchItem.IP.ToString() select si).SingleOrDefault();
                }
                catch
                {
                    throw new InvalidOperationException("Fehler bei Switchsuche - 2 Switches vorhanden");
                }
                
                if(tempSwitch != default(SwitchDB)) //if no switch was found, skip it
                {
                    if (switchItem.Mac != null && switchItem.Port != null && switchItem.ConnectionFailed == false)
                    {
                        //for every device which is connected
                        for (int i = 0; i < switchItem.Mac.Length; i++)
                        {
                            #region Write device
                            try
                            {
                                #region temporary Variables
                                Device tempQueryDev = (from p in devices where p.GetType() != typeof(Switch) && p.Mac[0] == switchItem.Mac[i] select p).SingleOrDefault();

                                DeviceDB tempDev = default(DeviceDB);
                                Vlan tempVlan = (from vlan in currentVlans where vlan.VlanNumber == switchItem.Vlan select vlan).SingleOrDefault();

                                Mac tempMac = (from mac in currentMacs where switchItem.Mac[i] == mac.MacAdress select mac).SingleOrDefault();

                                SwitchPort tempSP = (from sp in currentPorts where sp.SwitchId == tempSwitch.Id && sp.Name == switchItem.Port[i] select sp).SingleOrDefault();

                                Relocation tempRelocation = default(Relocation);
                                PatchPanel tempPatchPanel = default(PatchPanel);
                                Room tempRoom = default(Room);
                                #endregion

                                #region Get Switchport ID or create Switchport
                                if (tempSP == default(SwitchPort)) //if no switchport was found, create new one
                                {
                                    tempSP = new SwitchPort(); //initialize object
                                    tempSP.SwitchId = tempSwitch.Id; //set switch id to port
                                    tempSP.Name = switchItem.Port[i]; //set port name
                                    tempSP.VlanId = tempVlan.Id; //set vlan id
                                    tempSP.ChangeFlag = (int)SwitchPortFlags.SwitchportCreated; //set flag to new created
                                    tempSP.Id = databaseConnection.InsertIntoSwitchPort(tempSP); //update database
                                    currentPorts.Add(tempSP); // update local list

                                    #region Set Flag to Switch Object
                                    tempSwitch.ChangeFlag |= (int)SwitchFlags.SwitchportChanged; //set flag to switch, that at least one switchport changed
                                    #endregion
                                }
                                #endregion

                                if (tempQueryDev != default(Device)) //if no corresponding query device was found, skip it
                                {
                                    #region Get/set device Information from/to Database

                                    if (tempMac == default(Mac)) //if no mac entry was found, create new mac and device
                                    {
                                        //first create device, because mac needs id of device (database key)

                                        #region initialize a new Device
                                        tempDev = new DeviceDB(); //initialize device object
                                        tempDev.ChangeFlag = (int)DeviceFlags.NotFoundInInventory; //set flag to: created
                                        tempDev.SupplierId = defaultSupplierID; //default: unknown Supplier

                                        #region Get Device Type ID or create Device Type
                                        //get device type from database which fit to the current query object
                                        Database.DeviceType tempType = (from dt in currentDeviceTypes where dt.Description == tempQueryDev.GetType().Name select dt).FirstOrDefault();

                                        if (tempType == default(Database.DeviceType))//if not found, create new one
                                        {
                                            tempType = new Database.DeviceType(); //initialize new object
                                            tempType.Description = tempQueryDev.GetType().Name; //set name
                                            tempType.Id = databaseConnection.InsertIntoDeviceType(tempType); //update database
                                            currentDeviceTypes.Add(tempType); //update local list
                                        }
                                        #endregion

                                        tempDev.DeviceTypeId = tempType.Id; //save device type ID
                                        int index = databaseConnection.InsertIntoDevice(tempDev); //update database
                                        tempDev.Id = index; //set id for local object
                                        currentDevices.Add(tempDev); //update local list
                                        #endregion

                                        #region initialize Mac
                                        tempMac = new Mac(); //initialize new mac object
                                        tempMac.MacAdress = tempQueryDev.Mac[0]; //set mac address to current query device mac address
                                        tempMac.DeviceId = index; //set device ID

                                        tempMac.SwitchPortId = tempSP.Id; //set switchport ID to mac object
                                        tempMac.Id = databaseConnection.InsertIntoMac(tempMac); //update database
                                        currentMacs.Add(tempMac); //update local list

                                        #endregion
                                    }
                                    else //mac entry found
                                    {
                                        //get device to mac entry
                                        tempDev = (from d in currentDevices where d.Id == tempMac.DeviceId select d).SingleOrDefault();

                                        if (tempDev == default(DeviceDB)) //if no device found, create one
                                        {
                                            #region initialize a new Device
                                            tempDev = new DeviceDB(); //initialize device object
                                            tempDev.ChangeFlag = (int)DeviceFlags.NotFoundInInventory; //set flag to: created
                                            tempDev.SupplierId = defaultSupplierID; //default: unknown Supplier

                                            #region Get Device Type ID or create Device Type
                                            //get device type from database which fit to the current query object
                                            Database.DeviceType tempType = (from dt in currentDeviceTypes where dt.Description == tempQueryDev.GetType().Name select dt).FirstOrDefault();

                                            if (tempType == default(Database.DeviceType))//if not found, create new one
                                            {
                                                tempType = new Database.DeviceType(); //initialize new object
                                                tempType.Description = tempQueryDev.GetType().Name; //set name
                                                tempType.Id = databaseConnection.InsertIntoDeviceType(tempType); //update database
                                                currentDeviceTypes.Add(tempType); //update local list
                                            }
                                            #endregion

                                            tempDev.DeviceTypeId = tempType.Id; //save device type ID
                                            int index = databaseConnection.InsertIntoDevice(tempDev); //update database
                                            tempDev.Id = index; //set id for local object
                                            currentDevices.Add(tempDev); //update local list
                                            #endregion
                                        }
                                        else
                                        {
                                            Database.DeviceType type = (from dt in currentDeviceTypes where dt.Id == tempDev.DeviceTypeId select dt).SingleOrDefault();
                                            Type tempDevType = tempQueryDev.GetType();
                                            if (type == default(Database.DeviceType) || (type.Description != tempDevType.Name && tempDevType != typeof(Device)))
                                            {
                                                #region Get Device Type ID or create Device Type
                                                //get device type from database which fit to the current query object
                                                Database.DeviceType tempType = (from dt in currentDeviceTypes where dt.Description == tempQueryDev.GetType().Name select dt).FirstOrDefault();

                                                if (tempType == default(Database.DeviceType))//if not found, create new one
                                                {
                                                    tempType = new Database.DeviceType(); //initialize new object
                                                    tempType.Description = tempQueryDev.GetType().Name; //set name
                                                    tempType.Id = databaseConnection.InsertIntoDeviceType(tempType); //update database
                                                    currentDeviceTypes.Add(tempType); //update local list
                                                }
                                                #endregion

                                                tempDev.DeviceTypeId = tempType.Id; //save device type ID
                                                tempDev.ChangeFlag |= (int)DeviceFlags.DeviceTypeChanged; //set flag to: created
                                            }
                                        }
                                    }
                                    #endregion

                                    #region Check Vlans
                                    if (tempVlan != default(Vlan)) //if no vlan was found fatal error, cant be checked, no update possible, because vlan entry is essential for switch initialization, no changes in that list
                                    {
                                        if (tempSP.VlanId != tempVlan.Id) //if vlan changed
                                        {
                                            tempSP.VlanId = tempVlan.Id; //update switchport
                                            tempSP.ChangeFlag |= (int)SwitchPortFlags.VlanChanged; //set changeflag to switchport: vlan changed
                                            databaseConnection.UpdateSwitchPort(tempSP.Id, tempSP); //update database
                                        }
                                    }
                                    #endregion

                                    #region Check Location

                                    //search a relocation entry for the current device
                                    tempRelocation = (from rel in currentRelocations where rel.DeviceId == tempDev.Id orderby rel.Date descending select rel).FirstOrDefault();

                                    if (tempMac.SwitchPortId != tempSP.Id || tempRelocation == default(Relocation)) //switchport changed --> location changed
                                    {
                                        tempMac.SwitchPortId = tempSP.Id; //save new switchport
                                        tempDev.ChangeFlag |= (int)DeviceFlags.SwitchportChanged; //set change flag


                                        #region Update relocation
                                        //get patchpanel entry, to the switchport
                                        tempPatchPanel = (from panel in currentPatchPanels where panel.SwitchPortId == tempSP.Id select panel).SingleOrDefault();

                                        if (tempPatchPanel != default(PatchPanel)) //if an entry was found, search the corresponding room
                                            tempRoom = (from room in currentRooms where room.Id == tempPatchPanel.RoomId select room).SingleOrDefault();

                                        //if no errors at the room search sas found
                                        if (tempPatchPanel != default(PatchPanel) && tempRoom != default(Room))
                                        {
                                            if (tempRelocation == default(Relocation)) //if no entry was found, create one
                                            {
                                                tempRelocation = new Relocation();
                                                tempRelocation.DeviceId = tempDev.Id; //save device Id
                                            }
                                            tempRelocation.RoomIsId = tempRoom.Id; //set room id, to actual room
                                            tempRelocation.Date = DateTime.Now; //set date
                                            tempRelocation.Id = databaseConnection.InsertIntoRelocation(tempRelocation); //update database
                                            currentRelocations.Add(tempRelocation); //update local list
                                        }
                                        else //room search had an error
                                        {
                                            tempDev.ChangeFlag |= (int)DeviceFlags.RoomPatchpanelNotFound; //set flag to device
                                        }
                                        #endregion
                                    }
                                    #endregion

                                    #region Check device Information
                                    if (tempQueryDev.ConnectionFailed == true)
                                    {
                                        tempDev.ChangeFlag |= (int)DeviceFlags.ConnectionFailed; //set flag
                                    }
                                    else
                                    {
                                        #region check addition info an change
                                        Type devType = tempQueryDev.GetType();
                                        if (devType == typeof(Pc))
                                        {
                                            #region PC Update
                                            Pc tempPC = (Pc)tempQueryDev; //cast object to pc

                                            int RamID = GetAdditionTypeID("RAM", currentAdditionTypes); //get id of Ram
                                            WriteAdditionalInformationWithChange(RamID, currentAddition, tempDev, tempPC.RAM.ToString(), (int)DeviceFlags.RamChanged); //check if changes occured and update database and local list

                                            int OsID = GetAdditionTypeID("OS", currentAdditionTypes); //get id of Os
                                            WriteAdditionalInformationWithChange(OsID, currentAddition, tempDev, tempPC.OS, (int)DeviceFlags.OSChanged); //check if changes occured and update database and local list

                                            int uatID = GetAdditionTypeID("UserAtTime", currentAdditionTypes); //get id of user at time
                                            WriteAdditionalInformationWithChange(uatID, currentAddition, tempDev, tempPC.UserAtTime, (int)DeviceFlags.None); //check if changes occured and update database and local list set no flag or notice
                                            #endregion
                                        }
                                        else if (devType == typeof(Printer))
                                        {
                                            #region Printer Update
                                            Printer tempPrinter = ((Printer)tempQueryDev); //cast object to printer

                                            int lcID = GetAdditionTypeID("LifeCount", currentAdditionTypes);
                                            WriteAdditionalInformationWithChange(lcID, currentAddition, tempDev, tempPrinter.LifeCount, (int)DeviceFlags.None);

                                            int pidID = GetAdditionTypeID("PaperInputDescription", currentAdditionTypes);
                                            WriteAdditionalInformationWithChange(pidID, currentAddition, tempDev, tempPrinter.PaperInputDescription, (int)DeviceFlags.PaperInputDescriptionChanged); //check if changes occured and update database and local list

                                            int serialNumberID = GetAdditionTypeID("SerialNumber", currentAdditionTypes);
                                            WriteAdditionalInformationWithChange(serialNumberID, currentAddition, tempDev, tempPrinter.SerialNumber, (int)DeviceFlags.SerialNumberChanged); //check if changes occured and update database and local list

                                            int stateID = GetAdditionTypeID("State", currentAdditionTypes);
                                            WriteAdditionalInformationWithChange(stateID, currentAddition, tempDev, tempPrinter.State, (int)DeviceFlags.PrinterStateChanged); //check if changes occured and update database and local list

                                            int tonerID = GetAdditionTypeID("Toner", currentAdditionTypes);
                                            WriteAdditionalInformationWithChange(tonerID, currentAddition, tempDev, tempPrinter.Toner, (int)DeviceFlags.TonerChanged); //check if changes occured and update database and local list

                                            int tonerSupplyLevelID = GetAdditionTypeID("TonerSupplyLevel", currentAdditionTypes);
                                            WriteAdditionalInformationWithChange(tonerSupplyLevelID, currentAddition, tempDev, tempPrinter.TonerSupplyLevel, (int)DeviceFlags.None); //check if changes occured and update database and local list set no flag or notice

                                            int tonerSupplyMaximumID = GetAdditionTypeID("TonerSupplyMaximum", currentAdditionTypes);
                                            WriteAdditionalInformationWithChange(tonerSupplyMaximumID, currentAddition, tempDev, tempPrinter.LifeCount, (int)DeviceFlags.TonerSupplyMaximumChanged); //check if changes occured and update database and local list
                                            #endregion
                                        }
                                        #endregion
                                    }
                                    databaseConnection.UpdateDevice(tempDev.Id, tempDev); //update device (note, flag, added or changed information)
                                    databaseConnection.UpdateMac(tempMac.Id, tempMac); //update mac entry
                                    AddCustomResults(tempDev, tempQueryDev, errorID, currentAddition, currentAdditionTypes); //add all other custom results and check for changes
                                    #endregion
                                }
                            }
                            catch (Exception ex)
                            {
                                databaseUpdateSuccessful = false;
                                eventLog.WriteEntry(ex.Message + "\r\n" + ex.StackTrace, System.Diagnostics.EventLogEntryType.Error);
                                #region Update Error Flag
                                int tmpErrorFlag = databaseConnection.GetFlag(gpfID); //get progress flag
                                tmpErrorFlag |= (int)GlobalFlags.UnknownError; //add database update successfull flag
                                databaseConnection.UpdateFlag(gpfID, tmpErrorFlag); //update database
                                #endregion
                            }
                            #endregion
                        }
                    }
                    else
                    {
                        tempSwitch.ChangeFlag |= (int)SwitchFlags.ConnectionFailed;
                    }
                }

                databaseConnection.UpdateSwitch(tempSwitch.Id, tempSwitch); //if all devices has been updated, update switch object
            }
            #endregion
            foreach (Device dev in devices.RemovedDevices)
            {
                Mac tempMac = (from mac in currentMacs where dev.Mac[0] == mac.MacAdress select mac).SingleOrDefault();
                if (tempMac != default(Mac))
                {
                    DeviceDB tempDev = (from d in currentDevices where d.Id == tempMac.DeviceId select d).SingleOrDefault();
                    if (tempDev != default(DeviceDB))
                    {
                        tempDev.ChangeFlag |= (int)DeviceFlags.RemovedFromQueryList;
                        databaseConnection.UpdateDevice(tempDev.Id, tempDev); //update device (note, flag, added or changed information)
                    }
                }
                
            }
            #region Update Finish Flag
            if (databaseUpdateSuccessful)
            {
                int tmpFlag = databaseConnection.GetFlag(flagID); //get progress flag
                tmpFlag |= (int)QueryProgress.DataBaseUpdateSuccessfull; //add database update successfull flag
                databaseConnection.UpdateFlag(flagID, tmpFlag); //update database
            }
            #endregion

            while (!databaseConnection.CloseConnection()) ; //close connection, repeat until it works
        }

        /// <summary>
        /// Get the ID of the default supplier (unknown)
        /// </summary>
        /// <returns>ID</returns>
        private int GetDefaultSupplierID()
        {
            int id = databaseConnection.GetSupplierIdByName("unknown supplier");
            if (id == supplierNotFound)
            {
                Supplier sp = new Supplier();
                sp.ContactSalesOffice = "-";
                sp.ContactService = "-";
                sp.Name = "unknown supplier";
                sp.Note = "default supplier";
                id = databaseConnection.InsertIntoSupplier(sp);
            }
            return id;
        }

        /// <summary>
        /// Writes the progress into the Database
        /// </summary>
        /// <param name="progress">current Progress</param>
        private void WriteFullscanProgressToDatabase(QueryProgress progress)
        {
            if (!databaseConnection.OpenConnection()) //open connection
            {
                eventLog.WriteEntry("Could not Connect to Database", System.Diagnostics.EventLogEntryType.Error);
                return;
            }
            databaseConnection.UpdateFlag(flagID, (int)progress); //write progress to database 
        }
        #endregion

        #region overwritten Base Methods
        protected override void OnStart(string[] args) //called when service starts
        {
            Initialize();
            base.OnStart(args); //continue with default start method
        }

        protected override void OnStop() //called when service stops
        {
            if (!databaseConnection.OpenConnection()) //open database connection
            {
                eventLog.WriteEntry("Could not Connect to Database", System.Diagnostics.EventLogEntryType.Error);
                return;
            }

            gpfID = databaseConnection.InsertIntoFlag((int)GlobalFlags.ServiceStopped, "ServiceStateChanged", DateTime.Now); //add flag to table
            timer.Stop(); //stop timer

            while (!databaseConnection.CloseConnection()) ; //close connection, repeat until it works
            base.OnStop(); //continue with default stop method
        }

        protected override void OnContinue() //called when service continues
        {
            if (!databaseConnection.OpenConnection()) //open database connection
            {
                eventLog.WriteEntry("Could not Connect to Database", System.Diagnostics.EventLogEntryType.Error);
                return;
            }

            gpfID = databaseConnection.InsertIntoFlag((int)GlobalFlags.ServiceContinued, "ServiceStateChanged", DateTime.Now); //add flag to table
            timer.Start(); //start timer

            while (!databaseConnection.CloseConnection()) ; //close connection, repeat until it works
            base.OnContinue();//continue with default continue method
        }

        protected override void OnPause() //called when service pauses
        {
            if (!databaseConnection.OpenConnection()) //open database connection
            {
                eventLog.WriteEntry("Could not Connect to Database", System.Diagnostics.EventLogEntryType.Error);
                return;
            }

            gpfID = databaseConnection.InsertIntoFlag((int)GlobalFlags.ServicePaused, "ServiceStateChanged", DateTime.Now); //add flag to table
            timer.Stop(); //stop timer

            while (!databaseConnection.CloseConnection()) ; //close connection, repeat until it works
            base.OnPause();//continue with default pause method
        }

        protected override void OnCustomCommand(int command) //called when custom command is given
        {
            if (!databaseConnection.OpenConnection()) //open database connection
            {
                eventLog.WriteEntry("Could not Connect to Database", System.Diagnostics.EventLogEntryType.Error);
                return;
            }
            Command customCommand = (Command)command; //convert int to enum
            switch (customCommand) //switch enum
            {
                case Command.ReloadSettings:
                    gpfID = databaseConnection.InsertIntoFlag((int)GlobalFlags.None, "UpdateServiceSettings", DateTime.Now); //add flag to table

                    try
                    {
                        GetSettingsFromDatabase(); //get and apply settings
                        UpdateSwitches(); //get and update switches
                    }
                    catch (Exception ex)
                    {
                        eventLog.WriteEntry(ex.Message + ex.StackTrace); //write error to eventLog
                        #region Update Error Flag
                        int tmpFlag = databaseConnection.GetFlag(gpfID); //get progress flag
                        tmpFlag |= (int)GlobalFlags.FailedToApplySettings; //add database update error flag
                        databaseConnection.UpdateFlag(gpfID, tmpFlag); //update database
                        #endregion
                    }

                    #region Update Flag
                    int tmpSettingsFlag = databaseConnection.GetFlag(gpfID); //get progress flag
                    tmpSettingsFlag |= (int)GlobalFlags.FinishedUpdateSettings; //add database update flag
                    databaseConnection.UpdateFlag(gpfID, tmpSettingsFlag); //update database
                    #endregion
                    break;

                case Command.Search:
                    ExecuteFullScan(); //starts full scan
                    break;

                case Command.ChangeConfigFile:
                    gpfID = databaseConnection.InsertIntoFlag(0, "UpdateServiceSettings", DateTime.Now); //add flag to table
                    
                    GetCustomQueriesFromDatabase(); //get all custom queries from databasse and apply

                    #region Update Flag
                    int tmpQueriesFlag = databaseConnection.GetFlag(gpfID); //get progress flag
                    tmpQueriesFlag |= (int)GlobalFlags.FinishedUpdateCustomQueries; //add database update flag
                    databaseConnection.UpdateFlag(gpfID, tmpQueriesFlag); //update database
                    #endregion
                    break;
            }
            while (!databaseConnection.CloseConnection()) ; //close connection, repeat until it works
        }
        #endregion
        #endregion
    }
}
